import datetime
import json
import random

import pymysql
import requests
from snowflake import client

# mysql连接启动命令: snowflake_start_server --address=localhost --port=8910 --dc=1 --worker=1
connect = pymysql.connect(host='127.0.0.1', user='root', password='1905120210', port=3306, database='video-recommendation-system')
cursor = connect.cursor()
headers = {
    'Accept': '*/*',
    'Accept-Encoding': 'gzip, deflate, br, zstd',
    'Accept-Language': 'zh-CN,zh;q=0.9',
    'Origin': 'https://space.bilibili.com',
    'Priority': 'u=1, i',
    'Referer': 'https://space.bilibili.com/1528320969/video',
    'Sec-Ch-Ua': '"Google Chrome";v="125", "Chromium";v="125", "Not.A/Brand";v="24"',
    'Sec-Ch-Ua-Mobile': '?0',
    'Sec-Ch-Ua-Platform': '"Windows"',
    'Sec-Fetch-Dest': 'empty',
    'Sec-Fetch-Mode': 'cors',
    'Sec-Fetch-Site': 'same-site',
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/125.0.0.0 Safari/537.36'
}
cookies = {
    "buvid3": "4801BEAA-3DFB-D9AE-5556-97F83D30491E64006infoc",
    "b_nut": "1736647564",
    "_uuid": "6BA4C24A-5B8D-E8B5-47F6-335B5529E7D664855infoc",
    "header_theme_version": "CLOSE",
    "enable_web_push": "DISABLE",
    "buvid4": "459C9636-EE60-0103-2661-43DEC490540564971-025011202-GpO0ooDfzNOGRZ2ChuLHuzJQeNeIt9K%2B90Gh2T8yliDjfmmtxlF28QXZk0roEX9c",
    "LIVE_BUVID": "AUTO2517366476515173",
    "DedeUserID": "1528320969",
    "DedeUserID__ckMd5": "a7afddbec068536a",
    "rpdid": "0zbfVG41Ze|RHvaVXWS|1MY|3w1TwXsx",
    "hit-dyn-v2": "1",
    "CURRENT_QUALITY": "80",
    "PVID": "2",
    "enable_feed_channel": "ENABLE",
    "fingerprint": "5d7ed884f8edd5f9ea3654793536d3e2",
    "buvid_fp_plain": "undefined",
    "buvid_fp": "5d7ed884f8edd5f9ea3654793536d3e2",
    "home_feed_column": "4",
    "browser_resolution": "1034-835",
    "SESSDATA": "13a739cf%2C1757833734%2C83f89%2A32CjAsfbTeyxuWyDzV1rljsCsa5BisO9wDb8RBY6b_L2PRWD_hXgqZopH-utNjqe9UQbESVmEzWGFwb2x5aFFVTFhsWEJKaFFTU2NmZVNaOUQ0NXBhemt2RFBuMVY1UVpzVG11SjdEbDJtME9HMXZhWksza3dQYm9kOEJwTVVKN0lweWo4ZDl1RVBnIIEC",
    "bili_jct": "3c030a9f3b3c1505d5ec79f20bcb2e27",
    "sid": "6uqkleq0",
    "b_lsid": "3145EE87_195A8211F4B",
    "CURRENT_FNVAL": "4048",
    "bili_ticket": "eyJhbGciOiJIUzI1NiIsImtpZCI6InMwMyIsInR5cCI6IkpXVCJ9.eyJleHAiOjE3NDI1NDI2NDgsImlhdCI6MTc0MjI4MzM4OCwicGx0IjotMX0.eRyIbQQWE6HwNzNvwLOaRe57Srx5i56bqpDNWjtvXrU",
    "bili_ticket_expires": "1742542588",
    "bp_t_offset_1528320969": "1045588571072757760"
}
# 修改vid和url
pre = "https://api.bilibili.com"
url = [
    "/x/v2/reply/wbi/main?oid=114138554566310&type=1&mode=3&pagination_str=%7B%22offset%22:%22%22%7D&plat=1&seek_rpid=&web_location=1315875&w_rid=8c23992ad8559bfe451692c7c111f9e5&wts=1742296408",
    "/x/v2/reply/wbi/main?oid=114064231500736&type=1&mode=3&pagination_str=%7B%22offset%22:%22%22%7D&plat=1&seek_rpid=&web_location=1315875&w_rid=4c556797d989ff3ab42a253f9f43a719&wts=1742296491",
    "/x/v2/reply/wbi/main?oid=113849533466706&type=1&mode=3&pagination_str=%7B%22offset%22:%22%22%7D&plat=1&seek_rpid=&web_location=1315875&w_rid=406dc24b75b22dd3c86de6984e0a65f2&wts=1742296524",
    "/x/v2/reply/wbi/main?oid=807684615&type=1&mode=3&pagination_str=%7B%22offset%22:%22%22%7D&plat=1&seek_rpid=&web_location=1315875&w_rid=96a151671f56366058215227506f8e74&wts=1742296573",
    "/x/v2/reply/wbi/main?oid=208626759&type=1&mode=3&pagination_str=%7B%22offset%22:%22%22%7D&plat=1&seek_rpid=&web_location=1315875&w_rid=aa233c70a6c13938299897d403c373cd&wts=1742296600",
    "/x/v2/reply/wbi/main?oid=848522233&type=1&mode=3&pagination_str=%7B%22offset%22:%22%22%7D&plat=1&seek_rpid=&web_location=1315875&w_rid=49a5621e39e09051102b1bd45d5c4abf&wts=1742296617",
    "/x/v2/reply/wbi/main?oid=113134840978219&type=1&mode=3&pagination_str=%7B%22offset%22:%22%22%7D&plat=1&seek_rpid=&web_location=1315875&w_rid=dd4cb8ce7061f16ca8db4729dce9ec8c&wts=1742296645",
    "/x/v2/reply/wbi/main?oid=113134706694291&type=1&mode=3&pagination_str=%7B%22offset%22:%22%22%7D&plat=1&seek_rpid=&web_location=1315875&w_rid=d14d07b26a92922ae2cb0a2ee4a5e5a5&wts=1742296671",
    "/x/v2/reply/wbi/main?oid=113134756960965&type=1&mode=3&pagination_str=%7B%22offset%22:%22%22%7D&plat=1&seek_rpid=&web_location=1315875&w_rid=cd5ada0d443ae9574dc83d5571078705&wts=1742296688",
    "/x/v2/reply/wbi/main?oid=333947570&type=1&mode=3&pagination_str=%7B%22offset%22:%22%22%7D&plat=1&seek_rpid=&web_location=1315875&w_rid=d373bc5cdc1b3afec7fc9a13c252ec4f&wts=1742296704"
]
# 循环视频
for i in range(0,10):
    get = json.loads(requests.get(pre+url[i], headers=headers,cookies=cookies).text)
    arr = get["data"]['replies']
    for a in arr:
        # 1.先根据视频的评论信息生成对应用户
        # 防止用户重复
        name = a["member"]["uname"]
        cursor.execute('select * from user where name = %s', name)
        fetchall = cursor.fetchall()
        account = client.get_guid()
        password = client.get_guid()
        deleted = 0
        if not fetchall:
            # 防止手机号重复
            phone_number = '1' + "".join(random.choices("3456789", k=1)) + "".join(random.choices("0123456789", k=9))
            cursor.execute('select * from user where phone_number = %s', phone_number)
            while cursor.fetchall():
                phone_number = '1' + "".join(random.choices("3456789", k=1)) + "".join(random.choices("0123456789", k=9))
            # 防止用户简介为空
            introduction = a["member"]["sign"]
            if introduction == '-' or introduction == '':
                introduction = "这个用户什么都没说~"
            avatar_url = a["member"]["avatar"]
            create_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            deleted_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            try:
                cursor.execute('insert into user(name,account,password,phone_number,introduction,avatar_url,create_time,deleted,deleted_time) value (%s,%s,%s,%s,%s,%s,%s,%s,%s)',( name, account, password, phone_number, introduction, avatar_url, create_time, deleted,deleted_time))
                connect.commit()
            except Exception as e:
                print('user插入出现问题：%s' % (e))
                connect.rollback()
            uid = cursor.lastrowid
            # 2.根据评论信息生成评论
            create_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            user_comment = a["content"]["message"]
            # 判断评论长度是否大于255
            if len(user_comment) >= 255:
                user_comment = "真棒"
            cid = 0
            user_id = 0
            deleted_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            try:
                cursor.execute('insert into comment(uid,vid,create_time,user_comment,cid,user_id,deleted,deleted_time) value (%s,%s,%s,%s,%s,%s,%s,%s)',(uid, i+1, create_time, user_comment, cid, user_id,deleted,deleted_time))
                connect.commit()
            except Exception as e:
                print('comment插入出现问题：%s' % (e))
                connect.rollback()